package com.experienceBaby.Cashbook.greendao;

import com.experienceBaby.Cashbook.bean.Daily;
import com.experienceBaby.Cashbook.bean.Ranking;
import com.experienceBaby.Cashbook.bean.RankingList;
import com.experienceBaby.Cashbook.greendao.db.RecodeDao.Properties;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.experienceBaby.Cashbook.greendao.db.RecodeDao;
import com.experienceBaby.Cashbook.greendao.db.DaoMaster;
import com.experienceBaby.Cashbook.greendao.db.DaoSession;
import com.experienceBaby.Cashbook.greendao.db.Recode;
import com.experienceBaby.Cashbook.utils.CostUtils;
import com.experienceBaby.Cashbook.utils.DateUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 查询每周，每月，每年
 * 每周中每个type排名，点进去还会显示这周中type的remark排名
 */
public class DbController {
    /**
     * Helper
     */
    private DaoMaster.DevOpenHelper mHelper;//获取Helper对象
    /**
     * 数据库
     */
    private SQLiteDatabase db;
    /**
     * DaoMaster
     */
    private DaoMaster mDaoMaster;
    /**
     * DaoSession
     */
    private DaoSession mDaoSession;
    /**
     * 上下文
     */
    private Context context;
    /**
     * dao
     */
    private RecodeDao RecodeDao;

    private static DbController mDbController;

    /**
     * 获取单例
     */
    public static DbController getInstance(Context context) {
        if (mDbController == null) {
            synchronized (DbController.class) {
                if (mDbController == null) {
                    mDbController = new DbController(context);
                }
            }
        }
        return mDbController;
    }

    /**
     * 初始化
     *
     * @param context
     */
    public DbController(Context context) {
        this.context = context;
        mHelper = new DaoMaster.DevOpenHelper(context, "recode.db", null);
        mDaoMaster = new DaoMaster(getWritableDatabase());
        mDaoSession = mDaoMaster.newSession();
        RecodeDao = mDaoSession.getRecodeDao();
    }

    /**
     * 获取可读数据库
     */
    public SQLiteDatabase getReadableDatabase() {
        if (mHelper == null) {
            mHelper = new DaoMaster.DevOpenHelper(context, "recode.db", null);
        }
        SQLiteDatabase db = mHelper.getReadableDatabase();
        return db;
    }

    /**
     * 获取可写数据库
     *
     * @return
     */
    private SQLiteDatabase getWritableDatabase() {
        if (mHelper == null) {
            mHelper = new DaoMaster.DevOpenHelper(context, "recode.db", null);
        }
        SQLiteDatabase db = mHelper.getWritableDatabase();
        return db;
    }

    /**
     * 会自动判定是插入还是替换
     *
     * @param Recode
     */
    public void insertOrReplace(Recode Recode) {
        RecodeDao.insertOrReplace(Recode);
    }

    /**
     * 插入一条记录，表里面要没有与之相同的记录
     *
     * @param Recode
     */
    public long insert(Recode Recode) {
        return RecodeDao.insert(Recode);
    }

    /**
     * 更新数据
     *
     * @param Recode
     */
    public void update(Recode Recode) {
        Recode mOldRecode = RecodeDao.queryBuilder().where(Properties.Id.eq(Recode.getId())).build().unique();//拿到之前的记录
        if (mOldRecode != null) {
            RecodeDao.update(mOldRecode);
        }
    }

    public Recode searchById(int id) {
        return RecodeDao.queryBuilder()
                .where(Properties.Id.eq(id))
                .build().unique();
    }

    /**
     * 按条件查询数据
     * <p>
     * 支出、收入类型
     */
    private List<Recode> searchByType(String type) {
        return (List<Recode>) RecodeDao.queryBuilder()
                .where(Properties.Type.eq(type))
                .build().list();
    }

    /**
     * 按条件查询数据
     * <p>
     * 时间
     */
    private List<Recode> searchByDate(Long date) {
        Long[] time = DateUtils.getToday(date);
        return (List<Recode>) RecodeDao.queryBuilder()
                .where(Properties.Date.between(time[0], time[1]))
                .build().list();
    }

    /**
     * @param date     时间
     * @param isIncome 1：收入     0：支出
     * @return
     */
    private List<Recode> searchByDate(Long date, boolean isIncome) {
        Long[] time = DateUtils.getToday(date);
        return (List<Recode>) RecodeDao.queryBuilder()
                .where(Properties.Date.between(time[0], time[1]), Properties.Income.ge(isIncome ? 1 : 0))
                .build().list();
    }

    //TODO 消费种类排名，根据月，年，还需要根据支出，收入排序。
    //TODO 先查找一次group by,count(type) order by desc。降序查出种类名，然后再带着名字去查询，
    //TODO 点击之后再根据，时间，种类，降序查找

    /**
     * 如果查询的是年，则只需要更改两边time的时间即可，支出使用boolean控制
     *
     * @param year
     * @param isIncome
     * @return
     */
    public Ranking searchByYearGroupByType(int year, boolean isIncome) {
        Date date = DateUtils.getDate(year, 1, 1);
        Long[] time = DateUtils.getThisYear(date.getTime());
        Cursor c = mDbController.getReadableDatabase().rawQuery("SELECT TYPE,sum(COST) AS s FROM RECODE where DATE between " + time[0] + " AND " + time[1] + " AND INCOME=" + (isIncome ? 1 : 0) + " GROUP BY TYPE ORDER BY s DESC;", null);
        //每一行都是一个种类
        Ranking ranking = new Ranking();
        double totalCost = 0;
        List<RankingList> rankingLists = new ArrayList<>();
        while (c.moveToNext()) {
            RankingList rankingList = new RankingList();
            rankingList.setCost(c.getDouble(1));
            totalCost += c.getDouble(1);
            rankingList.setType(c.getString(0));
            rankingLists.add(rankingList);
        }
        for (RankingList rankingList : rankingLists) {
            rankingList.setPercentage(rankingList.getCost() / totalCost);
        }
        ranking.setDatas(rankingLists);
        ranking.setLotalCost(totalCost);
        ranking.setAvgCost(totalCost / 12);
        Double[] costs = new Double[13];
        //添加今年每个月的消费
        Cursor c1 = mDbController.getReadableDatabase().rawQuery("select MONTH,DATE,sum(COST) as s FROM RECODE where DATE between " + time[0] + " AND " + time[1] + " AND INCOME=" + (isIncome ? 1 : 0) + " group by MONTH order by MONTH asc", null);
        while (c1.moveToNext()) {
            costs[new Date(c1.getLong(1)).getMonth() + 1] = c1.getDouble(2);
        }
        ranking.setCosts(Arrays.asList(costs.clone()));
        return ranking;
    }

    /**
     * 查询哪年有数据
     *
     * @return
     */
    public List<Integer> searchYear() {
        List<Integer> years = new ArrayList<>();
        Cursor cursor = mDbController.getReadableDatabase().rawQuery("SELECT YEAR FROM RECODE group by YEAR order by YEAR ASC", null);
        while (cursor.moveToNext()) {
            years.add(cursor.getInt(0));
        }
        return years;
    }

    public Ranking searchByMonthGroupByType(int year, int month, boolean isIncome) {
        Date date = DateUtils.getDate(year, month, 1);
        Long[] time = DateUtils.getThisMonth(date.getTime());
        Cursor c = mDbController.getReadableDatabase().rawQuery("SELECT TYPE,sum(COST) AS s FROM RECODE where DATE between " + time[0] + " AND " + time[1] + " AND INCOME=" + (isIncome ? 1 : 0) + " GROUP BY TYPE ORDER BY s DESC;", null);
        //每一行都是一个种类
        Ranking ranking = new Ranking();
        double totalCost = 0;
        List<RankingList> rankingLists = new ArrayList<>();
        while (c.moveToNext()) {
            RankingList rankingList = new RankingList();
            rankingList.setCost(c.getDouble(1));
            totalCost += c.getDouble(1);
            rankingList.setType(c.getString(0));
            rankingLists.add(rankingList);
        }
        for (RankingList rankingList : rankingLists) {
            rankingList.setPercentage(rankingList.getCost() / totalCost);
        }
        ranking.setDatas(rankingLists);
        ranking.setLotalCost(totalCost);
        ranking.setAvgCost(totalCost / DateUtils.getDayOfMonth(date.getTime()));
        Double[] costs = new Double[DateUtils.getDayOfMonth(date.getTime()) + 1];
        //添加这个月每天的消费
        Cursor c1 = mDbController.getReadableDatabase().rawQuery("select DATE,sum(COST) as s FROM RECODE where DATE between " + time[0] + " AND " + time[1] + " AND INCOME=" + (isIncome ? 1 : 0) + " group by S_DATE order by DATE asc", null);
        while (c1.moveToNext()) {
            costs[new Date(c1.getLong(0)).getDate()] = c1.getDouble(1);
        }
        ranking.setCosts(Arrays.asList(costs.clone()));
        return ranking;
    }

    /**
     * @param year
     * @param month
     * @param isIncome 1：收入     0：支出
     * @return
     */
    @SuppressWarnings("deprecation")
    public List<Daily> searchByMonth(int year, int month, boolean isIncome) {
        List<Daily> dailies = new ArrayList<>();
        for (int i = DateUtils.getDayOfMonth(DateUtils.getDate(year, month, 1).getTime()); i >= 1; i--) {
            Date date = DateUtils.getDate(year, month, 1);
            Daily daily = new Daily();
            date.setDate(i);
            List<Recode> recodes = searchByDate(date.getTime(), isIncome);
            if (recodes.size() == 0) {
                continue;
            }
            //每日时间
            daily.setDate(date);
            //每日的数据
            daily.setDatas(recodes);
            //设置每日总消费
            daily.setCost(CostUtils.Count(recodes));
            dailies.add(daily);
        }
        return dailies;
    }

    /**
     * @param year  年
     * @param month 月
     * @return
     */
    @SuppressWarnings("deprecation")
    public List<Daily> searchByMonth(int year, int month) {
        List<Daily> dailies = new ArrayList<>();
        for (int i = DateUtils.getDayOfMonth(DateUtils.getDate(year, month, 1).getTime()); i >= 1; i--) {
            Date date = DateUtils.getDate(year, month, 1);
            Daily daily = new Daily();
            date.setDate(i);
            List<Recode> recodes = searchByDate(date.getTime());
            if (recodes.size() == 0) {
                continue;
            }
            //每日时间
            daily.setDate(date);
            //每日的数据
            daily.setDatas(recodes);
            //设置每日总消费
            daily.setCost(CostUtils.Count(recodes));
            dailies.add(daily);
        }
        return dailies;
    }


    /**
     * 查询所有数据
     */
    public List<Recode> searchAll() {
        return RecodeDao.queryBuilder().list();
    }

    /**
     * 删除数据
     */
    public void delete(String id) {
        RecodeDao.queryBuilder()
                .where(Properties.Id.eq(id))
                .buildDelete().executeDeleteWithoutDetachingEntities();
    }


}
